 EasyCalc

EasyCalc

EasyCalc is a spreadsheet program storing its data in XML format. FEATURES: html reports (including print-preview), report templates, large range of functions (mathematical, string, logical, if) , cell formatting using standard css format.


 Updates

Updates

EasyCalc is written by Jan Verhoeven with Delphi 5.

Email

jan1.verhoeven@wxs.nl

WebSite

http://jansfreeware.com


 Introduction

Introduction

There are various spreadsheet programs available, both commercial and freeware. Most of them require a heavy installation. EasyCalc can be used without installation.

File Format

EasyCalc uses the XML data format. This means that you can edit EasyCalc files in any plain text editor or xml editor.

Row and Column size

You can change the width of a column by dragging the column border. The height of a row is always autosized. Text is wrapped as needed to fit into a given column width. This is the same behaviour as in a HTML table.

Editing cells

To edit a cell you select the cell and then press the F2 key, click a second time or press the enter key. Type Ctrl+Enter to insert a line break in the cell text. To stop editing a cell press the Enter key or click in an other cell.

Selecting a range of cells

To select a range of cells you put EasyCalc in selection mode by pressing the F5 key. The statusbar will tell you whether you are in Edit Mode or in Range Select mode. Selected cells have a light yellow background.

Cell properties

EasyCalc allows you to set the properties of a cell or a range of cells using the property inspector. Cell formatting is done using standard cascading style sheet syntax. You can display the property inspector by pressing the F3 key. See Cell Properties.

Cell formula

You can enter a formula in a cell using the Cell Properties Inspector. Formulas are recalculated automatically when you change the value of any cell in the table. You can force recalculation by pressing the F9 key or by selecting Options - Recalculate from the menu.


 Cell Properties

Cell Properties

Cell properties can be changed using the property inspector which you can display by pressing the F3 key.

Bold, Italic, Underline

You can toggle the bold, italic and underline format of selected cells by clicking the bold, italic or underline button.

Cell Borders

You can set any or all of the 4 cell borders by clicking the corresponding cell border button. Click the no borders button to remove all borders.

Text align

Make the text left, center or right aligned using the corresponding align button.

Number of decimals

Increase or decrease the number of decimals of any displayed number using the increase decimals or decrease decimals button.

Cell Name

A cell can be given a name that can be used in formulas to refer to that cell. Please note that the entered name is only applied to the cell when you press the Enter key. Make sure that you do not use a reserved keyword for the name.

Cell Color

The background-color of a cell can be set by using the Cell Color Selector.

Text Color

The text color of a cell can be set by using the Text Color Selector.

Font Name and Size

You can set the font name and size of a cell by selecting the name from the Font-name dropdown list and/or the size from the font-size dropdown list.

Cell formula

You can add a formula to a cell by entering the formula in the Cell Formula field. You can also modify or delete the formula. Please note that you have to press the Enter key to apply the entered formula to the selected cell.

Cell Text

Although you can modify the text of a cell directly in the cell, you may find it easier to modify long pieces of text using the property inspector. Each character that you type is directly entered in the cell. The row height is automatically adjusted to fit the text in the current cell width.


 Formulas

Formulas

In formulas you can use the following basic mathematical operators:

^ * / + - ( )

Use brackets to group parts of the formula together for evaluation.

Tip

If a formule does not behave as expected it sometimes help to add extra brackets around functions:

if(isnumeric(c1),'yes','no') may not work but:

if((isnumeric(c1)),'yes','no') will work.

In addition you can use comparison operators, logical operators and several functions as detailed below.

Comparison operators

You can use the following comparison operators:

>	greater than

<	smaller than

=	equal

>=	greater than or equal

<=	less than or equal

<>	not equal

Logical operators

The following logical operators can be used in formulas.

and	logical and

or	logical or

not	logical not

like	logical test: model like '%bike%'

if	logical if: if (test,whentrue, whenfalse)

Note that the if operator can be nested. Both the whentrue part and the whenfalse part can contain if operators. Make sure you use round brackets in that case to force calculation of the inner if before the outer if.

Numerical Functions

sin(value)	returns sine of operand

cos(value)	returns cosine of operand

sqr(value)	returns square of operand

sqrt(value)	returns square root of operand

ceil(value)	returns operand rounded up toward positive infinity.

floor(value)	returns operand rounded toward negative infinity.

Cell References

In a formula you can refer to any cell using the rc notation: the first column in the first row can be refered to with r1c1.

You can refer to a cell on the same row using the c notation. c2 refers to the cell in column c2 on the same row as the cell containing the formula.

You can refer to a cell in the same column using the r notation. r3 refers to the cell in row r3 in the same column as the cell containing the formula.

You can refer to a cell using the name of the cell. You can set the name of a cell using the Cell Property Inspector.

If you have a cell named price, you can enter a formula like price*1.1.

You can refer to the column of a named cell using the c_cellname notation. If you have named a column price you can refer to the cell in the price column on the same row as the cell containing the formula by using c_price. This makes it easy to give the cells in the first row (column) names and refer to those columns in a readable manner.

In EasyCalc the first row contains the column names. Often you want to use the value of a cell (on the same row) in a named column in a formula. This is like calculated fields in a database. If you have named a header cell price you can refer to the cell at the same row in the named column with c_price.

In addition the following cell references are available:

cleft	returns the value of the cell left to the calculated cell

cabove	returns the value of the cell above the calculated cell

Statistical Functions

rsum	sums the values of all cells to the left of the calculated cell.

csum	sums the values of all cells above the calculated cell.

ravg	calculates the average of all cells to the left of the calculated cell. Only cells with valid numerical data are included.

cavg	calculates the average of all cells above the calculated cell. Only cells with valid numerical data are included.

rmax	returns the maximum value of the cells to the left of the calculated cell.

rmin	returns the minimum value of the cells to the left of the calculated cell.

cmax	returns the maximum value of the cells above the calculated cell.

cmin	returns the minimum value of the cells above the calculated cell.

String Functions

isnumeric(string)

returns true (-1) when string is a valid floating point number, otherwise returns false (0).

upper(string)

returns the uppercase value.

lower(string)

returns the lowercase value.

trim(string)

returns the value with surrounding spaces removed.

left(string,count)

returns the left part of a string: left(c1,4)

right(string,count)

returns the right part of a string: right(c1,2)

mid(string,from,count)

returns the mid part of a string: mid(c1,3,2)

replace(source,findtext,replacetext)

returns the source text with findtext replaced by replacetext: replace(c1,'a','b')

substr_before(sourcetext,findtext)

returns the text in sourcetext before findtext:

substr_before(c1,'n')

substr_after(sourcetext,findtext)

returns the text in sourcetext after findtext:

substr_after(c1,'ja')

length(string)

returns the length of a string:

length(c1)

Date functions

easter(year)

returns the easter date in the given year in yyyy-mm-dd format:

easter(2003) returns: 2003-04-20

year(datestring)

returns the year part of a date string in yyyy-mm-dd format;

month(datestring)

returns the month part of a date string in yyyy-mm-dd format;

day(datestring)

returns the day part of a date string in yyyy-mm-dd format;

weeknumber(datestring)

returns the week number of a date string in yyyy-mm-dd format;

date

returns the current date in yyyy-mm-dd format.

time

returns the current time in hh:nn:ss format (hours, minutes, seconds).

isdate(datestring)

returns -1 when datestring is a valid date. Datestring must be in the yyyy-mm-dd format. Returns 0 when datestring is not a valid date.


 Reports

Reports

Press Ctrl+R to create a HTML formatted report of your spreadsheet.

The report is displayed on the Report tab and can be printed with Report - Print and saved with File - Save Report As...

Load a report (or any other HTML document) from file with File - Open Report.

Using a report template

When you want to create a report based on a template you must first load the template with Report - Load Template. Next you can create a report based on the template with Report - Report from Template

The report template is a normal html document with a [data] placeholder in it. When the report is created the [data] placeholder is replaced by the spreadsheet in html format as a <table> and shown in the report viewer.

Subsequent use of Report from Template will be based on the last loaded template. Select a new template whenever you want to base the report on another template or use Report - Report to create a non-template based report only showing the bare table.